﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO;

namespace DAO
{
    public class HoaDAO
    {
        public List<HoaDTO> LayDanhSachHoa()
        {
            List<HoaDTO> dsHoa = new List<HoaDTO>();
            SqlConnection cn = DataProvider.ConnectionData();
            string sql = "sp_LayDanhSachHoa";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            HoaDTO hoa;
            while (dr.Read())
            {
                hoa = new HoaDTO();
                hoa.MaHoa = int.Parse(dr["MaHoa"].ToString());
                hoa.TenHoa = dr["TenHoa"].ToString();
                hoa.XuatXu = dr["XuatXu"].ToString();
                hoa.LoaiHoa = int.Parse(dr["MaLoai"].ToString());
                hoa.NhaCungCap = int.Parse(dr["MaNCC"].ToString());
                hoa.TinhTrang = int.Parse(dr["TinhTrang"].ToString());

                dsHoa.Add(hoa);
            }
            cn.Close();
            return dsHoa;
        }
        public List<HoaDTO> LayDanhSachTenHoa()
        {
            List<HoaDTO> dsHoa = new List<HoaDTO>();
            SqlConnection cn = DataProvider.ConnectionData();
            string sql = "sp_LayDanhSachHoa";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataReader dr = cmd.ExecuteReader();
            HoaDTO hoa;
            while (dr.Read())
            {
                hoa = new HoaDTO();
                hoa.MaHoa = int.Parse(dr["MaHoa"].ToString());
                hoa.TenHoa = dr["TenHoa"].ToString();
                dsHoa.Add(hoa);
            }
            cn.Close();
            return dsHoa;
        }


        public DataTable LayDSHoa()
        {
            DataTable dt = new DataTable();
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_LayDanhSachHoa", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public void ThemHoa(HoaDTO hoa)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_ThemHoa", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tenHoa", hoa.TenHoa);
            cmd.Parameters.Add("@xuatXu", hoa.XuatXu);
            cmd.Parameters.Add("@maLoai", hoa.LoaiHoa);
            cmd.Parameters.Add("@maNCC", hoa.NhaCungCap);

            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public void CapNhatHoa(HoaDTO hoa)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_CapNhatHoa", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHoa", SqlDbType.NVarChar);
            cmd.Parameters.Add("@tenHoa", SqlDbType.NVarChar);
            cmd.Parameters.Add("@xuatXu", SqlDbType.NVarChar);
            cmd.Parameters.Add("@maLoai", SqlDbType.NVarChar);
            cmd.Parameters.Add("@maNCC", SqlDbType.NVarChar);

            cmd.Parameters["@maHoa"].Value = hoa.MaHoa;
            cmd.Parameters["@tenHoa"].Value = hoa.TenHoa;
            cmd.Parameters["@xuatXu"].Value = hoa.XuatXu;
            cmd.Parameters["@maLoai"].Value = hoa.LoaiHoa;
            cmd.Parameters["@maNCC"].Value = hoa.NhaCungCap;

            cmd.ExecuteNonQuery();
            cn.Close();
        }


        public void XoaHoa(HoaDTO hoadto)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_XoaHoa", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHoa", SqlDbType.NVarChar);
            cmd.Parameters["@maHoa"].Value = hoadto.MaHoa;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        public DataTable TimKH(string HoTen, string SoDT)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_TimKiemKH", cn);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add("@HoTen", SqlDbType.NVarChar);
            cmd.Parameters.Add("@SoDT", SqlDbType.NChar);

            cmd.Parameters["@HoTen"].Value = HoTen;
            cmd.Parameters["@SoDT"].Value = SoDT;


            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public DataTable TimHoa(string TenHoa)
        {
            DataTable dt = new DataTable();
            SqlConnection cn = DataProvider.ConnectionData();
            SqlCommand cmd = new SqlCommand("sp_TimKiemHoa", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@tenHoa", TenHoa);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            cn.Close();
            return dt;
        }
        public HoaDTO TimKiemHoa(string tenHoa)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            string sql = "sp_TiemKiemTenHoa";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@TenHoa", tenHoa);
            SqlDataReader reader = cmd.ExecuteReader();
            HoaDTO h = new HoaDTO();
            reader.Read();
            h.MaHoa = int.Parse(reader["MaHoa"].ToString());
            h.TenHoa = reader["TenHoa"].ToString();
            h.XuatXu = reader["XuatXu"].ToString();
            h.LoaiHoa = int.Parse(reader["LoaiHoa"].ToString());
            h.NhaCungCap = int.Parse(reader["NhaCungCap"].ToString());
            
            return h;
        }
        public int LaySoLuongTonHoa(int maHoa)
        {
            SqlConnection cn = DataProvider.ConnectionData();
            string sql = "sp_LaySoLuongTon";
            SqlCommand cmd = new SqlCommand(sql, cn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@maHoa", maHoa);
            cmd.Parameters.Add("@soLuongTon", SqlDbType.Int);
            cmd.Parameters["@soLuongTon"].Direction = ParameterDirection.Output;
            cmd.Parameters["@soLuongTon"].Value = 0;
            cmd.ExecuteNonQuery();
            int kq = Int32.Parse(cmd.Parameters["@soLuongTon"].Value.ToString());
            cn.Close();
            return kq;
        }
    }
}
